//
//  YWDetectAndUpkeepDBManager.m
//  maintain
//
//  Created by kyo on 17/3/10.
//  Copyright © 2017年 ewin. All rights reserved.
//

#import "YWDetectAndUpkeepDBManager.h"
#import "YWDBTool.h"
#import "YWReplyDBManager.h"

#import "YWReply.h"
#import "YWCommonTool.h"

@implementation YWDetectAndUpkeepDBManager

+ (FMDatabase*)fmdb{
    return [[YWDBTool shareDBTool] fmdb];
}
+ (FMDatabaseQueue*)dbQeue{
    return [[YWDBTool shareDBTool] dbQeue];
}

#pragma mark - 维保任务(检测,保养)表
NSString *const KSqlCreateDetectAndUpkeepMissionTable = @"CREATE TABLE IF NOT EXISTS 'mission_maintenance' (missionId integer NOT NULL,missionSequence varchar,missionName varchar,startTime timestamp,cutoffTime timestamp,createTime timestamp,updateTime timestamp,maintenanceTypeId integer NOT NULL,missionStatus integer NOT NULL,status smallint,note varchar,equipmentQuantity integer DEFAULT 0,completedQuantity integer DEFAULT 0,equipmentTypeId integer DEFAULT 0,uploadFail boolean DEFAULT 0,finishTime timestamp,read boolean DEFAULT 0,buildingId varchar,executorIds varchar,creatorId integer,missionCycle smallint,type smallint,observerIds varchar);";

+ (BOOL)createDetectAndUpkeepMissionTable{
    return [[self fmdb] executeUpdate:KSqlCreateDetectAndUpkeepMissionTable];
}

+ (void)dropAndReCreateDetectAndUpkeepMissionTable{
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"drop table if exists mission_maintenance;";
        if ([db executeUpdate:sql]) {
            [db executeUpdate:KSqlCreateDetectAndUpkeepMissionTable];
        }
    }];
}

#pragma mark - 维保任务的设备表
+ (BOOL)createDetectAndUpkeepMissionEquipmentTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS 'mission_maintenance_equipment' (missionId integer NOT NULL,equipmentId varchar)";
    return [[self fmdb] executeUpdate:sql];
}

+ (BOOL)updateDetectAndUpkeepEqipments:(NSArray*)equipmentArray missionId:(NSInteger)missioId set:(FMResultSet*)set db:(FMDatabase*)db{
    if (missioId == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSString *sql = @"DELETE FROM 'mission_maintenance_equipment' where missionId=?";
    executeResult = [db executeUpdate:sql,@(missioId)];
    if (executeResult) {
        [equipmentArray enumerateObjectsUsingBlock:^(YWEquipmentIdJson*  _Nonnull equipmentIdJson, NSUInteger idx, BOOL * _Nonnull stop) {
            sql = @"INSERT INTO 'mission_maintenance_equipment' (missionId,equipmentId) VALUES (?, ?);";
            executeResult = [db executeUpdate:sql, @(missioId),equipmentIdJson.equipmentId];
            if (!executeResult) {
                *stop = YES;
            }
        }];
    }
    return executeResult;
}

+ (BOOL)updateDetectAndUpkeepMissionWithArray:(NSArray*)missions{
    if (missions.count == 0) {
        return YES;
    }
    __block BOOL executeResult = YES;
    __block NSArray* blockMissions = missions;
    __block NSString* sql = nil;
    //    __block NSMutableArray *apartmentsArray = [NSMutableArray array];
    //    __block NSMutableArray *floorsArray = [NSMutableArray array];
    //    __block NSMutableArray *buildingsArray = [NSMutableArray array];
    //__block NSMutableArray* replysArray = [NSMutableArray array];
    [self.dbQeue inTransaction:^(FMDatabase* db, BOOL* rollback) {
        [blockMissions enumerateObjectsUsingBlock:^(id  _Nonnull mission, NSUInteger idx, BOOL* _Nonnull stop) {
            if ([mission isKindOfClass:[YWMaintenanceMission class]]) {
                YWMaintenanceMission *maintenanceMission = (YWMaintenanceMission*)mission;
                sql = @"SELECT * FROM 'mission_maintenance' WHERE missionId=?;";
                FMResultSet* set = [db executeQuery:sql, @(maintenanceMission.missionId)];
                if ([set next]) {
                    if ((YWMissionStatusFinished != [set intForColumn:@"missionStatus"])) {
                        sql = @"UPDATE 'mission_maintenance' SET missionSequence =?,missionName =?,startTime =?,cutoffTime =?,createTime =?,updateTime =?,maintenanceTypeId =?,missionStatus =?,status =?,note =?,equipmentQuantity =?,completedQuantity =?,equipmentTypeId =?,finishTime =?,buildingId =?,executorIds =?,creatorId =?,missionCycle=?,type=?,observerIds=? WHERE missionId=?;";
                        //uploadFail,read不更新,创建表有默认值
                        executeResult = [db executeUpdate:sql, maintenanceMission.missionSequence,maintenanceMission.missionName,@(maintenanceMission.startTime),@(maintenanceMission.cutoffTime),@(maintenanceMission.createTime),@(maintenanceMission.updateTime),@(maintenanceMission.maintenanceTypeId),@(maintenanceMission.missionStatus),@(maintenanceMission.status),maintenanceMission.note,@(maintenanceMission.equipmentQuantity),@(maintenanceMission.completedQuantity),@(maintenanceMission.equipmentTypeId),@(maintenanceMission.finishTime),maintenanceMission.buildingId,maintenanceMission.executorIds,@(maintenanceMission.creator.uniqueId),@(maintenanceMission.missionCycle),@(maintenanceMission.type),maintenanceMission.observerIds,@(maintenanceMission.missionId)];
                    }
                }
                else {
                    //uploadFail,read不更新,创建表有默认值
                    sql = @"INSERT INTO 'mission_maintenance' (missionId,missionSequence,missionName,startTime,cutoffTime,createTime,updateTime,maintenanceTypeId,missionStatus,status,note,equipmentQuantity,completedQuantity,equipmentTypeId,finishTime,buildingId,executorIds,creatorId,missionCycle,type,observerIds) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
                    executeResult = [db executeUpdate:sql, @(maintenanceMission.missionId),maintenanceMission.missionSequence, maintenanceMission.missionName,@(maintenanceMission.startTime),@(maintenanceMission.cutoffTime),@(maintenanceMission.createTime),@(maintenanceMission.updateTime),@(maintenanceMission.maintenanceTypeId),@(maintenanceMission.missionStatus),@(maintenanceMission.status),maintenanceMission.note,@(maintenanceMission.equipmentQuantity),@(maintenanceMission.completedQuantity),@(maintenanceMission.equipmentTypeId),@(maintenanceMission.finishTime),maintenanceMission.buildingId,maintenanceMission.executorIds,@(maintenanceMission.creator.uniqueId),@(maintenanceMission.missionCycle),@(maintenanceMission.type),maintenanceMission.observerIds];
                }
                if (!executeResult) {
                    *stop = YES;
                    *rollback = YES;
                };
                //评论
                //sql = @"DELETE FROM 'reply' WHERE relationId=?";
                //[db executeUpdate:sql, @(maintenanceMission.missionId)];
//                [maintenanceMission.replies enumerateObjectsUsingBlock:^(YWReply*  _Nonnull reply, NSUInteger idx, BOOL * _Nonnull stop) {
//                    reply.relationId = maintenanceMission.missionId;
//                    reply.relationType = YWReplyRelationTypeDetectAndUpkeep;
//                }];
//                [replysArray addObjectsFromArray:maintenanceMission.replies];
                //更新任务的设备
                BOOL success = [YWDetectAndUpkeepDBManager updateDetectAndUpkeepEqipments:maintenanceMission.equipments missionId:maintenanceMission.missionId set:set db:db];
                if (!success) {
                    *stop = YES;
                    *rollback = YES;
                }
//                [maintenanceMission.executors enumerateObjectsUsingBlock:^(YWHandler*  _Nonnull handler, NSUInteger idx, BOOL * _Nonnull stop) {
//                    if (![self updateMissionHanler:handler set:set db:db]) {
//                        *stop = YES;
//                        *rollback = YES;
//                    }
//                }];
//                
//                if (![self updateMissionHanler:maintenanceMission.creator set:set db:db]) {
//                    *stop = YES;
//                    *rollback = YES;
//                }
            }
        }];
        [db closeOpenResultSets];
    }];
    //更新评论
    //[[YWReplyDBManager shareManager] updateReplyWithReplyArray:replysArray];
    return executeResult;
}

+ (NSArray*)detectAndUpkeepMissionWithStatus:(YWMissionFinishStatus)status maintenanceType:(YWMaintenanceType)type page:(NSInteger)page
{
    if (page==0) {
        return nil;
    }
    __block NSMutableArray* missionArray = [NSMutableArray array];
    __block NSInteger showCount = page * 10;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        //取维保任务
        NSString* sql = nil;
        if (status == YWMissionFinishStatusUnfinished) {
            sql = [NSString stringWithFormat:@"SELECT * FROM 'mission_maintenance' WHERE missionStatus = %lu AND maintenanceTypeId = %lu AND status=0 ORDER BY createTime DESC LIMIT %ld",(unsigned long)YWMissionStatusUnfinished, (unsigned long)type,(long)showCount];
        }else{
            sql = [NSString stringWithFormat:@"SELECT * FROM 'mission_maintenance' WHERE (missionStatus = %lu or missionStatus = %lu) AND maintenanceTypeId = %lu AND status=0 ORDER BY createTime DESC LIMIT %ld",(unsigned long)YWMissionStatusFinished,(unsigned long)YWMissionStatusExpire, (unsigned long)type, (long)showCount];
        }
        FMResultSet* set = [db executeQuery:sql];
        NSString *lastDate = nil;
        NSMutableArray *array = nil;
        while ([set next]) {
            YWMaintenanceMission* maintenanceMission = [self maintenanceMissionWithFMResultSet:set FMDatabase:db];
            NSString *timeDate = [YWCommonTool showChineseFormatterDate:maintenanceMission.createTime/1000];
//            if (status == YWMissionFinishStatusUnfinished) {
//                timeDate = [YWCommonTool showChineseFormatterDate:maintenanceMission.createTime/1000];
//            }else{
//                timeDate = [YWCommonTool showChineseFormatterDate:maintenanceMission.finishTime/1000];
//            }
            if (lastDate==nil || ![timeDate isEqualToString:lastDate]) {
                lastDate = timeDate;
                array = [NSMutableArray array];
            }
            [array addObject:maintenanceMission];
            if (![missionArray containsObject:array] && array.count) {
                [missionArray addObject:array];
            }
        }
        [db closeOpenResultSets];
    }];
    return missionArray;
}

+ (YWMaintenanceMission *)maintenanceMissionWithMissionId:(NSInteger)missionId{
    __block YWMaintenanceMission* mission = nil;
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT * FROM 'mission_maintenance' WHERE missionId = ? AND status=0";
        FMResultSet* set = [db executeQuery:sql, @(missionId)];
        if ([set next]) {
            mission = [self maintenanceMissionWithFMResultSet:set FMDatabase:db];
        }
        
        [db closeOpenResultSets];
    }];
    return mission;
}

+ (YWMaintenanceMission*)maintenanceMissionWithFMResultSet:(FMResultSet*)set FMDatabase:(FMDatabase*)db
{
    YWMaintenanceMission* mission = [[YWMaintenanceMission alloc] init];
    mission.missionId = [set longForColumn:@"missionId"];
    mission.missionSequence = [set stringForColumn:@"missionSequence"];
    if (mission.missionSequence.length == 0) {
        mission.missionSequence = @"无";
    }
    mission.missionName = [set stringForColumn:@"missionName"];
    mission.missionCycle = [set intForColumn:@"missionCycle"];
    mission.type = [set intForColumn:@"type"];
    mission.observerIds = [set stringForColumn:@"observerIds"];
    
    NSString* equSql = @"SELECT a.* FROM equipment a left join mission_maintenance_equipment b on a.equipmentId=b.equipmentId where b.missionId=?;";
    FMResultSet *equSet = [db executeQuery:equSql, @(mission.missionId)];
    NSMutableArray *equipmentArray = [NSMutableArray array];
    while ([equSet next]) {
        YWEquipment *equipment = [[YWDBTool shareDBTool] equipmentWithFMResultSet:equSet];
        [equipmentArray addObject:equipment];
    }
    mission.equipments = equipmentArray;
    
    mission.startTime = [set longLongIntForColumn:@"startTime"];
    mission.cutoffTime = [set longLongIntForColumn:@"cutoffTime"];
    mission.createTime = [set longLongIntForColumn:@"createTime"];
    mission.updateTime = [set longLongIntForColumn:@"updateTime"];
    mission.maintenanceTypeId = [set intForColumn:@"maintenanceTypeId"];
    mission.missionStatus = [set intForColumn:@"missionStatus"];
    mission.status = [set intForColumn:@"status"];
    mission.note = [set stringForColumn:@"note"];
    mission.equipmentQuantity = [set intForColumn:@"equipmentQuantity"];
    mission.completedQuantity = [set intForColumn:@"completedQuantity"];
    mission.equipmentTypeId = [set intForColumn:@"equipmentTypeId"];
    mission.uploadFail = [set boolForColumn:@"uploadFail"];
    mission.finishTime = [set longLongIntForColumn:@"finishTime"];
    mission.read = [set boolForColumn:@"read"];
    mission.buildingId = [set stringForColumn:@"buildingId"];
    mission.executorIds = [set stringForColumn:@"executorIds"];
    NSMutableArray *executorArray = [NSMutableArray array];
    NSString *executorSql = [NSString stringWithFormat:@"SELECT * FROM 'handler' WHERE uniqueId in (%@)",mission.executorIds];
    FMResultSet *executorSet = [db executeQuery:executorSql];
    while ([executorSet next]) {
        YWHandler *handler = [[YWDBTool shareDBTool] handlerWithSet:executorSet];
        [executorArray addObject:handler];
    }
    mission.executors = executorArray;
    
    NSInteger creatorId = [set intForColumn:@"creatorId"];
    NSString* sql = @"SELECT * FROM 'handler' WHERE uniqueId = ?;";
    FMResultSet *handlerSet = [db executeQuery:sql, @(creatorId)];
    if ([handlerSet next]) {
        mission.creator = [[YWDBTool shareDBTool] handlerWithSet:handlerSet];
    }
    NSMutableArray *observer = [NSMutableArray array];
    sql = [NSString stringWithFormat:@"SELECT * FROM 'handler' WHERE uniqueId in (%@)",mission.observerIds];
    handlerSet = [db executeQuery:sql];
    while ([handlerSet next]) {
        YWHandler *handler = [[YWDBTool shareDBTool] handlerWithSet:handlerSet];
        [observer addObject:handler];
    }
    mission.observers = observer;
    
    return mission;
}

+ (NSString*)allUnfinishDetectAndUpkeepMissionIds{
    NSMutableString *missionIds = [NSMutableString string];
    [self.dbQeue inDatabase:^(FMDatabase* db) {
        NSString* sql = @"SELECT missionId FROM 'mission_maintenance' WHERE missionStatus=? AND status = 0";
        FMResultSet* set = [db executeQuery:sql,@(YWMissionStatusUnfinished)];
        while ([set next]) {
            NSInteger missionId = [set intForColumn:@"missionId"];
            if (missionId>0) {
                [missionIds appendString:[@(missionId) stringValue]];
                [missionIds appendString:@","];
            }
        }
        [db closeOpenResultSets];
    }];
    return [NSString stringWithFormat:@"%@",missionIds];
}

@end
